import os
import pyodbc
import pandas as pd
import Get_HTMLtables as GH
import numpy as np
from _sqlite3 import connect
import openpyxl
from openpyxl.reader.excel import load_workbook
global tables
import pyodbc
import re
import os
class PDFTableExtractor:
    def __init__(self, ID, find_keyword,Html_file):
        self.ID = ID
        read_html_obj = GH.ReadHtml()  # 创建GH.ReadHtml实例
        self.ZBDW=read_html_obj.read_DW(Html_file,find_keyword)
    def get_EPBH(self):
        server = 'SHPUBSRV02'
        database = 'JYCFI'
        conn = pyodbc.connect(
            'Driver={SQL Server};Server=' + server + ';Database=' + database + ';Trusted_Connection=yes;')
        query = """
                   SELECT DISTINCT EPBH
                   FROM [10.106.22.51].JYPRIME.dbo.usrQYMB GG 
                   WHERE QYBH= ?
                       """
        IGSDM = str(self.IGSDM)
        self.EPBH = pd.read_sql(query, conn, params=[IGSDM])
        try:
            self.EPBH = self.EPBH['EPBH'][0]
        except:
            self.EPBH = 'EP未匹配成功，需要人工处理'

            return self.EPBH

    def get_GG(self):
        server = 'SHPUBSRV01'
        database = 'JYPRIME'
        conn = pyodbc.connect(
            'Driver={SQL Server};Server=' + server + ';Database=' + database + ';Trusted_Connection=yes;')
        ID = self.ID
        query = """
                                  SELECT ID,IGSDM,CONVERT(varchar(10),XXFBRQ,23) AS XXFBRQ,CONVERT(varchar(10),JZRQ,23) AS JZRQ,XXBT,GGLJ
                                  FROM usrGSGGYWFWB GG 
                                  WHERE ID= CAST(? AS NVARCHAR)
                                      """
        self.GG = pd.read_sql(query, conn, params=[ID])
        if not self.GG.empty:
            self.IGSDM = self.GG['IGSDM'][0]
            self.XXFBRQ = self.GG['XXFBRQ'][0]
            self.JZRQ = self.GG['JZRQ'][0]
            self.XXLY = self.GG['XXBT'][0]
            return self.GG
    def find_keyword_and_extract_table(self, Html_file, find_keyword):
        table = GH.ReadHtml.read_html_file(Html_file, find_keyword)
        if table.shape[1]!='0':
            table = table.replace('主要产品', 'SJLMSMC').replace(' ','',regex=True)
            try :
                table=table.drop(7,axis=1)
                if table[0][0] != '分行业情况' and table[0][0] == '分行业':
                    VALUE = ['分行业情况', '', '', '', '', '', '']
                    table.loc[-1] = VALUE
                    table.index = table.index + 1  # 更新索引
                    table = table.sort_index()  # 根据索引排序
                elif table[0][0] != '分产品情况' and table[0][0] == '分产品':
                    VALUE = ['分产品情况', '', '', '', '', '', '']
                    table.loc[-1] = VALUE
                    table.index = table.index + 1  # 更新索引
                    table = table.sort_index()  # 根据索引排序
            except:
                pass
        return table
    # 切割表格(营收)
    def sp(self,table):
        keywords = ['分行业情况','分产品情况']
        print('开始分割')
        if any(table.isin(['分行业情况', '分产品情况'])):
            new_df = table
            new_df.columns=range(len(new_df.columns))
            BASCI_split_indices = [0]
            new_df[0] = new_df[0].astype(str)
            split_indices = new_df[
                new_df[0].apply(lambda x: any(keyword in x for keyword in keywords))].index.tolist()
            split_indices = [int(str(index).replace('[', '').replace(']', '')) for index in split_indices]
            split_indices = BASCI_split_indices + split_indices
            if split_indices:
                split_indices.append(len(new_df))
                with pd.ExcelWriter('分割表格.xlsx', engine='xlsxwriter') as writer:
                    for i in range(0, len(split_indices)):
                        if split_indices[i] > 0:
                            dfs = new_df.iloc[split_indices[i - 1] + 1:split_indices[i]]
                            sheet_name = dfs[0].iloc[0]
                            dfs.to_excel(writer, sheet_name=sheet_name, index=False)
    # 获取代码类字段(营收)
    def get_code(self):
        db_name = r'\\10.3.2.15\固收部\其他\经营指标\自动化标准化存档\mydatabase.db'
        con = connect(db_name)
        Product = "SELECT * FROM product_code"
        self.PRODUCT_CODE = pd.read_sql(Product, con)
        ZB = 'SELECT * FROM ZB_CODE'
        self.ZB_CODE = pd.read_sql(ZB, con)
        DQ = 'SELECT * FROM city_code'
        self.DQ_CODE = pd.read_sql(DQ, con)
        BT='SELECT * FROM GG  '
        self.BT=pd.read_sql(BT,con)
        BZH='SELECT * FROM BZH  WHERE EPBH =?'
        self.BZH=pd.read_sql(BZH,con,params=[self.IGSDM])
        con.close()
    # 格式化为企业经营的底层表(营收)
    def xggs(self):
        excel_file_path = '分割表格.xlsx'
        new_excel_file_path = f'{self.EPBH}_{self.JZRQ}_成本分析表.xlsx'
        workbook = openpyxl.load_workbook(excel_file_path)
        sheet_names = workbook.sheetnames
        with pd.ExcelWriter(new_excel_file_path, engine='openpyxl') as writer:
            for i, sheet_name in enumerate(sheet_names, start=1):
                print(f'Processing sheet {i}')
                yysr = pd.read_excel(excel_file_path, sheet_name=sheet_name).replace(' ', '', regex=True)

                if '分产品' in sheet_names and '分行业' in sheet_names and sheet_name == '分行业':
                    continue
                new_columns = yysr.iloc[0].replace('分产品', 'SJLMYMC').replace('分行业', 'SJLMYMC').replace(
                    '成本构成项目', 'SJLMSMC').replace('本期占总成本比例(%)', '成本占比').replace('本期占主营业务成本比例(%)', '成本占比').\
                    replace('上年同期占总成本比例（%）','上年同期占总成本比例(%)')
                id_vars = ['SJLMYMC', 'SJLMSMC']
                yysr = yysr.rename(columns=new_columns)

                yysr = yysr.drop(0)
                new_columns=['SJLMYMC','SJLMSMC',]
                yysr = yysr.drop('上年同期金额', axis=1).drop('上年同期占总成本比例(%)', axis=1)
                yysr = pd.melt(yysr,
                               id_vars=['SJLMYMC', 'SJLMSMC'],
                               value_vars=list(yysr.columns)[1:],
                               var_name='ZBMC',
                               value_name='ZBSJ')
                yysr['JZRQ'] = None
                yysr['ZTYSMC'] = None
                yysr['SJLMY'] = None
                yysr['EPBH'] = None
                yysr['ZBDW'] = None
                yysr['SJLME'] = None
                yysr['SJLMS'] = None
                yysr['TJKJ'] = None
                yysr['XXFBRQ']=None
                yysr['SFYX'] = None
                yysr['SFYX'].iloc[:len(yysr)] = 'FCC000000006'
                conditions = [
                    (yysr['ZBMC'] == '本期金额'),
                    (yysr['ZBMC'] == ('本期金额较上年同期变动比例(%)')),
                    (yysr['ZBMC'] == '成本占比')
                ]
                values = ['当期值', '当期同比增长率','当期值']
                yysr['TJKJ'] = np.select(conditions, values, default='当期值')
                try:
                    yysr['SJLMYMC']=yysr['SJLMYMC'].replace('',method='ffill')
                    yysr['SJLMYMC'] = yysr['SJLMYMC'].fillna(method='ffill')
                except:

                    pass

                DW_VALUES = [self.ZBDW, '%','%']
                yysr['ZBDW'] = np.select(conditions, DW_VALUES)
                yysr['TJQJ'] = '年'
                yysr['JYYWLXDM'] = None
                yysr['XXLY'] = None
                yysr = yysr.replace('（%）', '', regex=True).replace('比上年增减', '', regex=True).replace('营业收入',
                                                                                                         '销售收入',
                                                                                                         regex=True).astype(
                    str)
                yysr['ZBSJ'] = yysr['ZBSJ'].str.replace('减少', '-', regex=True).replace('增加', '', regex=True) \
                    .replace('个百分点', '', regex=True).replace('不适用', '', regex=True).replace('/', '', regex=True)
                yysr['SJLMYDM'] = yysr.merge(self.PRODUCT_CODE, on='SJLMYMC', how='left')['标准代码']
                yysr['SJLMY'].iloc[:len(yysr)] = '产品'
                yysr['JYYWLXDM'].iloc[:len(yysr)] = '成本构成'

                yysr['SJLMYMC'] = yysr['SJLMYMC'].replace('合计', '产品合计', regex=True).replace('减:', '', regex=True)\
                .replace('总计','产品合计').replace('总计', '产品合计')
                yysr['SJLMSMC'] = yysr['SJLMSMC'].replace('合计', '').replace('减:', '', regex=True).replace('总计','').replace('nan','')
                empty_strings = yysr['SJLMSMC'].eq('')

                yysr.loc[yysr['SJLMSMC'].notna() & (yysr['SJLMSMC'] != '') & (yysr['SJLMSMC'].notnull()), 'SJLMS'] = '其他'
                yysr['ZBMC'] = yysr['ZBMC'].replace('本期金额', '成本').replace('本期金额较上年同期变动比例(%)', '成本')
                yysr['ZBDM'] = yysr.merge(self.ZB_CODE, on='ZBMC', how='left')['ZBDM']
                yysr['EPBH'].iloc[:len(yysr)] = str(self.EPBH)
                yysr['JZRQ'].iloc[:len(yysr)] = self.JZRQ
                yysr['XXLY'].iloc[:len(yysr)] = self.XXLY
                yysr['XXFBRQ'].iloc[:len(yysr)] =self.XXFBRQ
                yysr = yysr.reindex(
                    columns=['EPBH', 'XXLY', 'XXFBRQ', 'JZRQ', 'JYYWLXDM', 'SJLMY', 'SJLMYMC', 'SJLMYDM',
                             'SJLME', 'SJLMEMC', 'SJLMEDM', 'SJLMS', 'SJLMSMC', 'ZTYSMC', 'ZBDM', 'ZBMC', 'ZBSJ',
                             'ZBDW', 'TJKJ', 'TJQJ'])
                yysr = yysr.replace(' ', '').replace('None','').replace('nan','')
                yysr.to_excel(writer, index=False, sheet_name=sheet_name)
                return yysr
def run_all(ID,Html_file):
    Html_file=Html_file
    find_keyword = [['成本构成项目','分产品'],['成本构成项目','分项目'],['本期占总成本比例','分产品'],['本期占总成本比例','分行业']]
    extractor_instance = PDFTableExtractor(ID=ID, find_keyword=find_keyword,Html_file=Html_file)
    extractor_instance.get_GG()
    extractor_instance.get_EPBH()
    table = extractor_instance.find_keyword_and_extract_table(Html_file=Html_file, find_keyword=find_keyword)
    if table.shape[1] != 0:
        extractor_instance.get_code()
        extractor_instance.sp(table)
        table=extractor_instance.xggs()
        return table
    else:
        print('未找到关键词')
    return pd.DataFrame()

if __name__ == '__main__':
    ID='767482363957'
    Html_file = r'D:\软件打包\Juno-win32.win32.x86_64\Juno-win32.win32.x86_64\767482363957.HTML'
    run_all(ID, Html_file)